﻿using System;
using System.Collections.Generic;
using System.Text;
using RockCompany.Common.Lib;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;
using RockCompany.Common.Constant;
using RockCompany.Master.DTO;
using RockCompany.Master.Constant;

namespace RockCompany.Master.Model
{
    public class SW01FormModel : CommonModel
    {
        //private DBConnector dbConnector;
        private const string WHERE_CLAUSE = " SO_LO = @SO_LO";
        private const string WHERE_CLAUSE_2 = " CONG_LENH_CHA = @CONG_LENH_CHA";
        private const string WHERE_CLAUSE_3 = " SO_LO = @SO_LO" + " AND PHE_DUYET = 1";

        public SW01FormModel()
        {
            dbConnector = new DBConnector();
        }

        public bool ExistsLotNo(string newLotNo)
        {
            string Sql = "SELECT SO_LO" +
                        " FROM CONG_LENH_SX" +
                        " WHERE SO_LO = @SO_LO";

            DataTable res = new DataTable();
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            // Clear parameters
            dbConnector.removeParameters();

            // Add parameters
            dbConnector.addParameters("@" + SW01AConst.SO_LO, newLotNo);

            res = dbConnector.selectData(Sql);
            dbConnector.close();
            return CommonModel.existData(res); // If res has data, return true
        }

        #region Insert
        public bool InsertWorkOrder(WorkOrderDTO woDTO, string loginUserName)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            dbConnector.removeParameters();

            pv.Add(SW01AConst.SO_LO, woDTO.LotNo);
            pv.Add(SW01AConst.MA_MAU, woDTO.ProductNo);
            pv.Add(SW01AConst.TEN_SP, woDTO.ProductNo);
            pv.Add(SW01AConst.NGAY_SX, woDTO.ProductionDate);

            addDoubleParameters(pv, SW01AConst.DO_SANG_S, woDTO.DoSangS);
            addDoubleParameters(pv, SW01AConst.DO_SANG_N, woDTO.DoSangN);
            addDoubleParameters(pv, SW01AConst.MAU_S, woDTO.MauS);
            addDoubleParameters(pv, SW01AConst.MAU_N, woDTO.MauN);
            addDoubleParameters(pv, SW01AConst.DO_MAU_S, woDTO.DoMauS);
            addDoubleParameters(pv, SW01AConst.DO_MAU_N, woDTO.DoMauN);

            pv.Add(SW01AConst.SO_LAN_DIEU_CHINH, woDTO.SoLanDieuChinh);
            pv.Add(SW01AConst.CONG_LENH_CHA, woDTO.OrigianlLotNo);
            addDoubleParameters(pv, SW01AConst.HIEU_SUAT, woDTO.HieuSuat);
            pv.Add(SW01AConst.SO_E, woDTO.SoE);

            addDoubleParameters(pv, CONG_LENH_SX.N_SL_TON_AN_TOAN, woDTO.SLTonAnToan);
            pv.Add(SW01AConst.DIEU_CHINH, woDTO.WOType);
            pv.Add(SW01AConst.PHE_DUYET, woDTO.Status);

            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_PROGRAM_ID, "SW01");
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_USER, loginUserName);

            if (insertData(CONG_LENH_SX.N_TABLE, pv))  // True if 1 row is inserted
            {
                dbConnector.removeParameters();
                // Insert into WO details
                foreach (DataRow row in woDTO.MaterialList.Rows)
                {
                    pv.Clear();
                    pv.Add(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU, row[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]);
                    if (woDTO.WOType == SW01AConst.MODIFICATION_TYPE)
                    {
                        pv.Add(CONG_LENH_SX_CT.N_SO_LO_BAN_DAU, row[CONG_LENH_SX_CT.N_SO_LO]);
                    }
                    
                    pv.Add(CONG_LENH_SX.N_SO_LO, woDTO.LotNo);
                    pv.Add(CONG_LENH_SX_CT.N_TY_LE, row[CONG_LENH_SX_CT.N_TY_LE]);
                    pv.Add(CONG_LENH_SX_CT.N_TRONG_LUONG, row[CONG_LENH_SX_CT.N_TRONG_LUONG]);
                    pv.Add(CONG_LENH_SX_CT.N_SO_LAN, row[CONG_LENH_SX_CT.N_SO_LAN]);
                    pv.Add(CONG_LENH_SX_CT.N_TONG_LUONG_SX, row[CONG_LENH_SX_CT.N_TONG_LUONG_SX]);
                    pv.Add(CONG_LENH_SX_CT.N_GHI_CHU, row[CONG_LENH_SX_CT.N_GHI_CHU]);

                    pv.Add(CONG_LENH_SX_CT.N_EX_RATE, row[CONG_LENH_SX_CT.N_EX_RATE]);
                    pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_PROGRAM_ID, "SW01");
                    pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_TIME, DateTime.Now);
                    pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_USER, loginUserName);

                    insertData(CONG_LENH_SX_CT.N_TABLE, pv);
                }
                return true;
            }

            return false;
        }

        private bool InsertCLCT(WorkOrderDTO woDTO, string loginUser, DataRow row)
        {
            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU, row[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]);
            if (woDTO.WOType == SW01AConst.MODIFICATION_TYPE)
            {
                pv.Add(CONG_LENH_SX_CT.N_SO_LO_BAN_DAU, row[CONG_LENH_SX_CT.N_SO_LO]);
            }

            pv.Add(CONG_LENH_SX.N_SO_LO, woDTO.LotNo);
            pv.Add(CONG_LENH_SX_CT.N_TY_LE, row[CONG_LENH_SX_CT.N_TY_LE]);
            pv.Add(CONG_LENH_SX_CT.N_TRONG_LUONG, row[CONG_LENH_SX_CT.N_TRONG_LUONG]);
            pv.Add(CONG_LENH_SX_CT.N_SO_LAN, row[CONG_LENH_SX_CT.N_SO_LAN]);
            pv.Add(CONG_LENH_SX_CT.N_TONG_LUONG_SX, row[CONG_LENH_SX_CT.N_TONG_LUONG_SX]);
            pv.Add(CONG_LENH_SX_CT.N_GHI_CHU, row[CONG_LENH_SX_CT.N_GHI_CHU]);

            pv.Add(CONG_LENH_SX_CT.N_EX_RATE, row[CONG_LENH_SX_CT.N_EX_RATE]);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_PROGRAM_ID, "SW01");
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_USER, loginUser);

            return insertData(CONG_LENH_SX_CT.N_TABLE, pv);
        }
        #endregion

        public DataTable GetUnapprovedLotNos()
        {
            string Sql = "SELECT SO_LO" +
                        " FROM CONG_LENH_SX" +
                        " WHERE PHE_DUYET = 0";

            DataTable res = null;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            res = dbConnector.selectData(Sql);
            dbConnector.close();
            return res;
        }

        public DataTable CalculateProductQty(string soLo)
        {
            string sql = "SELECT SUM(ISNULL(SL_THUC_NHAP, 0)) AS SL_THUC_NHAP, MIN(CREATION_TIME) AS CREATION_TIME" +
                        " FROM PHIEU_NK_TP_CT" +
                        " WHERE SO_LO = '" + soLo + "'";

            return selectData(sql);
        }

        public DataTable SearchWO(WorkOrderDTO woDTO)
        {
            string Sql = "SELECT 0 AS chk, SO_LO, MA_MAU, NGAY_SX, PHE_DUYET, HIEU_SUAT, CONG_LENH_CHA" +
                        " , DO_SANG_S, DO_SANG_N, DO_MAU_S, DO_MAU_N, MAU_S, MAU_N, SO_E, SL_TON_AN_TOAN" +
                        " , TONG_TIEN, CASE WHEN DIEU_CHINH = 1 THEN '1'" +
                                " ELSE '0'" +
                        " END AS DIEU_CHINH" +
                        " FROM CONG_LENH_SX" +
                        " WHERE SO_LO LIKE @SO_LO" +
                        " AND MA_MAU LIKE @MA_MAU";

            DataTable res = null;
            if (dbConnector.getCurrentState() != ConnectionState.Open)
            {
                dbConnector.connect();
            }
            dbConnector.removeParameters();

            dbConnector.addParameters("@" + SW01AConst.SO_LO, woDTO.LotNo + "%");
            dbConnector.addParameters("@" + SW01AConst.MA_MAU, woDTO.ProductNo + "%");


            res = dbConnector.selectData(Sql);
            dbConnector.close();
            return res;
        }

        public DataTable SearchWODetails(WorkOrderDTO woDTO)
        {
            
            string sql = "SELECT CL.*, NL.DON_GIA, NL.TY_GIA" +
                        " FROM CONG_LENH_SX_CT CL, NGUYEN_VLIEU_VTU NL" +
                        " WHERE CL.MA_NVL_VTU = NL.MA_NVL_VTU" +
                            " AND CL.SO_LO = '" + woDTO.LotNo + "'";

            if (woDTO.WOType == SW01AConst.MODIFICATION_TYPE)
            {
                string whereClause = "SO_LO = " + "@" + CONG_LENH_SX_CT.N_SO_LO;
                Dictionary<string, object> para = new Dictionary<string, object>();
                para.Add(CONG_LENH_SX_CT.N_SO_LO, woDTO.LotNo);
                return selectWithWhereClause(CONG_LENH_SX_CT.N_TABLE, whereClause, para);
            }

            return selectData(sql);
        }

        #region Update
        private bool UpdateCLSX(WorkOrderDTO woDTO, string  loginUser)
        {
            string sClause = "";
            sClause += CONG_LENH_SX.N_SO_LO + " = @" + CONG_LENH_SX.N_SO_LO;

            Dictionary<string, object> updateValues = new Dictionary<string, object>();

            updateValues.Add(CONG_LENH_SX.N_SO_LO, woDTO.LotNo);
            updateValues.Add(CONG_LENH_SX.N_PHE_DUYET, woDTO.Status);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_TONG_TIEN, woDTO.TongTien);

            updateValues.Add(CONG_LENH_SX.N_NGAY_SX, woDTO.ProductionDate);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_DO_SANG_S, woDTO.DoSangS);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_DO_SANG_N, woDTO.DoSangN);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_DO_MAU_S, woDTO.DoMauS);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_DO_MAU_N, woDTO.DoMauN);

            addDoubleParameters(updateValues, CONG_LENH_SX.N_MAU_S, woDTO.MauS);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_MAU_N, woDTO.MauN);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_HIEU_SUAT, woDTO.HieuSuat);
            updateValues.Add(CONG_LENH_SX.N_SO_E, woDTO.SoE);
            addDoubleParameters(updateValues, CONG_LENH_SX.N_SL_TON_AN_TOAN, woDTO.SLTonAnToan);

            updateValues.Add(CONG_LENH_SX.N_LAST_UPDATE_TIME, DateTime.Now);
            updateValues.Add(CONG_LENH_SX.N_LAST_UPDATE_PROGRAM_ID, "SW01");
            updateValues.Add(CONG_LENH_SX.N_LAST_UPDATE_USER, loginUser);

            return updateData2(CONG_LENH_SX.N_TABLE, sClause, updateValues);
        }

        private bool UpdateCLCT(string lotNo, string loginUser, DataRow row)
        {
            string sClause = "";
            sClause += CONG_LENH_SX.N_SO_LO + " = @" + CONG_LENH_SX.N_SO_LO;
            sClause += " AND " + CONG_LENH_SX_CT.N_MA_NVL_VTU + " = @" + CONG_LENH_SX_CT.N_MA_NVL_VTU;

            Dictionary<string, object> pv = new Dictionary<string, object>();
            pv.Add(CONG_LENH_SX.N_SO_LO, lotNo);
            pv.Add(CONG_LENH_SX_CT.N_MA_NVL_VTU, row[CONG_LENH_SX_CT.N_MA_NVL_VTU]);
            addDoubleParameters(pv, CONG_LENH_SX_CT.N_TY_LE, Util.parseDouble(row[CONG_LENH_SX_CT.N_TY_LE].ToString()));

            addDecimalParameters(pv, CONG_LENH_SX_CT.N_TRONG_LUONG, row[CONG_LENH_SX_CT.N_TRONG_LUONG]);
            pv.Add(CONG_LENH_SX_CT.N_SO_LAN, row[CONG_LENH_SX_CT.N_SO_LAN]);
            addDecimalParameters(pv, CONG_LENH_SX_CT.N_TONG_LUONG_SX, row[CONG_LENH_SX_CT.N_TONG_LUONG_SX]);
            pv.Add(CONG_LENH_SX_CT.N_GHI_CHU, row[CONG_LENH_SX_CT.N_GHI_CHU]);

            addDecimalParameters(pv, CONG_LENH_SX_CT.N_EX_RATE, row[CONG_LENH_SX_CT.N_EX_RATE]);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_PROGRAM_ID, "SW01");
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_TIME, DateTime.Now);
            pv.Add(NGUYEN_VLIEU_VTU_DTO.N_CREATION_USER, loginUser);

            return updateData2(CONG_LENH_SX_CT.N_TABLE, sClause, pv);
        }

        public bool saveData(WorkOrderDTO woDTO, string sUserName)
        {
            DataTable tempDb = null;
            int insertRes = 0, updateRes = 0;

            tempDb = woDTO.MaterialList.GetChanges(DataRowState.Modified);
            int updatedRowCount = (tempDb != null ? tempDb.Rows.Count : 0);

            tempDb = woDTO.MaterialList.GetChanges(DataRowState.Added);
            int addedRowCount = (tempDb != null ? tempDb.Rows.Count : 0);

            tempDb = woDTO.MaterialList.GetChanges();
            if (tempDb != null)
            {
                foreach (DataRow curRow in tempDb.Rows)
                {
                    switch (curRow.RowState)
                    {
                        case DataRowState.Added:
                            if (InsertCLCT(woDTO,sUserName,curRow))
                                insertRes++;
                            break;
                        case DataRowState.Modified:
                            if (UpdateCLCT(woDTO.LotNo,sUserName,curRow))
                                updateRes++;
                            break;
                    }
                }
                woDTO.MaterialList.AcceptChanges();
            }
            return insertRes == addedRowCount && updateRes == updatedRowCount;
        }

        public bool UpdateWO(WorkOrderDTO woDTO, string loginUserName)
        {
            if (UpdateCLSX(woDTO, loginUserName))
            {
                return saveData(woDTO, loginUserName);
            }

            return false;
        }
        #endregion

        #region Delete
        public int DeleteWO(List<string> checkedList)
        {
            int removedCount = 0;

            Dictionary<string, object> para = new Dictionary<string, object>();
            Dictionary<string, object> para2 = new Dictionary<string, object>();

            foreach (string val in checkedList)
            {
                para.Clear();
                para2.Clear();
                para.Add(CONG_LENH_SX.N_SO_LO, val);
                para2.Add(CONG_LENH_SX.N_CONG_LENH_CHA, val);

                if (IsNewWO(val))
                {
                    // Delete WO details
                    removeRow(CONG_LENH_SX_CT.N_TABLE, " WHERE" + WHERE_CLAUSE, para);

                    // Delete WO
                    removeRow(CONG_LENH_SX.N_TABLE, " WHERE" + WHERE_CLAUSE, para);

                    removedCount++;
                }
            }
            return removedCount;
        }

        public bool DeleteCLCT(string lotNo, DataTable dt, List<DataRow> rowList)
        {
            string sWhereClause = " WHERE ";
            sWhereClause += CONG_LENH_SX.N_SO_LO + " ='" + lotNo + "' AND ";
            sWhereClause += CONG_LENH_SX_CT.N_MA_NVL_VTU + " IN(";
            Dictionary<string, object> pv = new Dictionary<string, object>();
            foreach (DataRow curRow in rowList)
            {
                if (curRow.RowState != DataRowState.Added)
                {
                    string sP = "@" + CONG_LENH_SX_CT.N_MA_NVL_VTU + rowList.IndexOf(curRow).ToString();
                    sWhereClause += sP + ",";
                    pv.Add(sP, curRow.Field<string>(CONG_LENH_SX_CT.N_MA_NVL_VTU));
                }
                dt.Rows.Remove(curRow);
            }
            
            if (pv.Count > 0)
            {
                sWhereClause = sWhereClause.Substring(0, sWhereClause.Length - 1) + ")";
                if (removeRow(CONG_LENH_SX_CT.N_TABLE, sWhereClause, pv))
                    pv.Clear();
            }
            return pv.Count == 0;
        }
        #endregion

        public bool IsNewWO(string lotNo)
        {
            Dictionary<string, object> para = new Dictionary<string, object>();
            Dictionary<string, object> para2 = new Dictionary<string, object>();

            para.Add(CONG_LENH_SX.N_SO_LO, lotNo);
            para2.Add(CONG_LENH_SX.N_CONG_LENH_CHA, lotNo);
            return !existData(selectWithWhereClause(CONG_LENH_SX.N_TABLE, WHERE_CLAUSE_2, para2)) &&
                    !existData(selectWithWhereClause(CONG_LENH_SX.N_TABLE, WHERE_CLAUSE_3, para)) &&
                    !existData(selectWithWhereClause("NHAT_KY_CL", WHERE_CLAUSE, para)) &&
                    !existData(selectWithWhereClause(PHIEU_LANH_NVL_CT_DTO.N_TABLE, WHERE_CLAUSE, para)) &&
                    !existData(selectWithWhereClause("PHIEU_NK_TP_CT", WHERE_CLAUSE, para)) &&
                    !existData(selectWithWhereClause("PHIEU_XH_CT", WHERE_CLAUSE, para)) &&
                    !existData(selectWithWhereClause("PHIEU_XK_TP_CT", WHERE_CLAUSE, para));
        }

        public bool export2Excel(string newPath, string lotNo)
        {
            bool b = false;
            if (!CommonConstantIF.BLANK.Equals(newPath))
            {
                WorkOrderDTO woDTO = new WorkOrderDTO(lotNo);
                DataTable wo = SearchWO(woDTO);
                DataTable woDetails = SearchWODetails(woDTO);
                if (CommonModel.existData(wo))
                {
                    ExcelExporter excelExport = new ExcelExporter();
                    string fileName = "Cong lenh sx phoi mau";
                    bool isNew = true;
                    if (wo.Rows[0][SW01AConst.DIEU_CHINH].Equals(SW01AConst.MODIFICATION_TYPE.ToString()))
                    {
                        fileName = "Cong lenh sx dieu chinh";
                        isNew = false;
                    }
                    excelExport.open(fileName);

                    excelExport.inputValue(new string[] { "E2", "T2" }, Util.FormatDate(DateTime.Today.Date), false);
                    excelExport.inputValue(new string[] { "D3", "G3" }, wo.Rows[0][CONG_LENH_SX.N_MA_MAU], false);
                    excelExport.inputValue(new string[] { "J3", "M3" }, wo.Rows[0][CONG_LENH_SX.N_SO_LO], false);
                    DateTime ngaySX=Util.parseDateTime(wo.Rows[0][CONG_LENH_SX.N_NGAY_SX]);
                    excelExport.inputValue(new string[] { "Q3", "T3" }, Util.FormatDate(ngaySX), false);

                    excelExport.inputValue(new string[] { "B23", "D23" }, wo.Rows[0][CONG_LENH_SX.N_DO_SANG_S], false);
                    excelExport.inputValue(new string[] { "B24", "D24" }, wo.Rows[0][CONG_LENH_SX.N_DO_SANG_N], false);
                    excelExport.inputValue(new string[] { "F23", "H23" }, wo.Rows[0][CONG_LENH_SX.N_MAU_S], false);
                    excelExport.inputValue(new string[] { "F24", "H24" }, wo.Rows[0][CONG_LENH_SX.N_MAU_N], false);
                    excelExport.inputValue(new string[] { "J23", "L23" }, wo.Rows[0][CONG_LENH_SX.N_MAU_N], false);
                    excelExport.inputValue(new string[] { "J24", "L24" }, wo.Rows[0][CONG_LENH_SX.N_DO_MAU_N], false);
                    excelExport.inputValue(new string[] { "M23", "P24" }, wo.Rows[0][CONG_LENH_SX.N_DO_MAU_N], false);

                    int startIndex = 5, count = 0, curIndex = 0;
                    foreach (DataRow curRow in woDetails.Rows)
                    {
                        curIndex = startIndex + count;
                        string r1 = isNew ? "D" : "E";
                        string r2 = isNew ? "G" : "H";

                        // Ma NVL / VT
                        excelExport.inputValue(new string[] { r1 + curIndex, r2 + curIndex }, curRow[CONG_LENH_SX_CT.N_MA_NVL_VTU], false);

                        // Ty le
                        r1 = isNew ? "H" : "K";
                        r2 = isNew ? "I" : "L";
                        excelExport.inputValue(new string[] { r1 + curIndex, r2 + curIndex }, Util.FormatDecimal(curRow[CONG_LENH_SX_CT.N_TY_LE]), false);

                        // Trong luong
                        r1 = isNew ? "J" : "M";
                        r2 = isNew ? "L" : "P";
                        excelExport.inputValue(new string[] { r1 + curIndex, r2 + curIndex }, curRow[CONG_LENH_SX_CT.N_TRONG_LUONG], false);

                        if (isNew)
                        {
                            excelExport.inputValue(new string[] { "M" + curIndex, "M" + curIndex }, curRow[CONG_LENH_SX_CT.N_SO_LAN], false);
                            excelExport.inputValue(new string[] { "N" + curIndex, "P" + curIndex }, "=J" + curIndex + "*M" + curIndex, false);
                        }
                        else
                        {
                            excelExport.inputValue(new string[] { "I" + curIndex, "J" + curIndex }, curRow[CONG_LENH_SX_CT.N_SO_LO], false);
                        }
                        excelExport.inputValue(new string[] { "Q" + curIndex, "T" + curIndex }, curRow[CONG_LENH_SX_CT.N_GHI_CHU], false);
                        count++;
                    }
                    
                    b = excelExport.saveExcel(newPath);
                    excelExport.close();
                }
            }
            return b;
        }

        #region Xây dựng danh sách các NVL đã chọn từ Tồn Kho và NVL
        private DataTable getMatsInfo()
        {
            string sClause = "SELECT *" +
                             " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE;
            return selectData(sClause);
        }

        public DataTable buildSelectedMatsList(DataTable storedMats)
        {
            string sMatsList = "";
            foreach (DataRow row in storedMats.Rows)
            {
                sMatsList += "'" + Util.parseString(row[NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU]) + "',";
            }
            sMatsList = sMatsList.Substring(0, sMatsList.Length - 1);

            string sSql = "SELECT A1." + TON_KHO_DTO.N_MA_HANG + " AS '" + NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + "',";
            sSql += "ISNULL(B1." + NGUYEN_VLIEU_VTU_DTO.N_DON_GIA + ",0) AS '" + NGUYEN_VLIEU_VTU_DTO.N_DON_GIA + "',";
            sSql += "ISNULL(B1." + NGUYEN_VLIEU_VTU_DTO.N_TY_GIA + ",'') AS '" + NGUYEN_VLIEU_VTU_DTO.N_TY_GIA + "'";
            sSql += " FROM((SELECT *";
            sSql += " FROM " + TON_KHO_DTO.N_TABLE;
            sSql += " WHERE " + TON_KHO_DTO.N_MA_HANG + " IN(" + sMatsList + ")) A1";
            sSql += " LEFT JOIN(SELECT *";
            sSql += " FROM " + NGUYEN_VLIEU_VTU_DTO.N_TABLE + ") B1";
            sSql += " ON A1." + TON_KHO_DTO.N_MA_HANG + "= B1." + NGUYEN_VLIEU_VTU_DTO.N_MA_NVL_VTU + ") ";
            return selectData(sSql);
        }
        #endregion
    }
}
